package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import main.Terminal;
import model.Customer;
import model.Sale;
/**
 * 
 * @author Jan
 * @editor Patrick
 */
public class DBCustomer {

	private DBConnection con;
	
	private final String phone = "Phone";
	private final String name = "Name";
	private final String saleID = "SaleID";
	
	private final String selectAll = "SELECT Phone, Name, SaleID FROM Customer";
	public DBCustomer()
	{
		con = DBConnection.getInstance();
	}
	
	public void addCustomer(Customer customer, Sale sale)
	{
		String query = String.format("INSERT INTO Customer VALUES ('%s', '%s', %d)", customer.getPhone(), customer.getName(), sale.getId());
		con.execute(query);
		Terminal.say(query);
	}
	
	public void deleteCustomer(Customer customer)
	{
		String query = ("DELETE FROM Customer WHERE " + phone + " = " + customer.getPhone());
		con.execute(query);
		Terminal.say(query);
	}
	
	public String whereAdderPhone(String phone)
	{
		return selectAll + " WHERE " + this.phone + " like '%" + phone + "%'";
	}
	
	public String whereAdderName(String name)
	{
		return selectAll +" WHERE " +this.name + " like '%" + name + "%'";
	}
	
	public Customer getCustomer(String phone)
	{
		ResultSet resSet = con.executeResSet(whereAdderPhone(phone));
		Customer c = null;
		try {
			while(resSet.next()) {
				c = buildCustomer(resSet);
			}
		} catch (SQLException e) {
			Terminal.say("Stack trace: ", e);
		}
		return c;
	}
	
//	public ArrayList<Customer> getCustomerByName(String name)
//	{
//		ArrayList<Customer> cust = new ArrayList<Customer>();
//		ResultSet resSet = con.executeResSet(whereAdderName(name));
//
//		Customer c;
//		do {
//			c = buildCustomer(resSet);
//			if(c != null) {
//				cust.add(c);
//			}
//		}
//		while(c != null);
//				
//		return cust;
//	}
	
	public ArrayList<Customer> getCustomerByName(String name)
	{
		ArrayList<Customer> cust = new ArrayList<Customer>();

		try {
			ResultSet resSet = con.executeResSet(whereAdderName(name));
			while(resSet.next()) {
				Customer c = buildCustomer(resSet);
				if(c != null) {
					cust.add(c);
				}
			}
			resSet.getStatement().close();
		} catch (SQLException e) {
			Terminal.say("Stack trace: ", e);
		}
				
		return cust;
	}
	
	public ArrayList<Customer> getCustomerByPhone(String phone)
	{
		ArrayList<Customer> cust = new ArrayList<Customer>();

		try {
			ResultSet resSet = con.executeResSet(whereAdderPhone(phone));
			while(resSet.next()) {
				Customer c = buildCustomer(resSet);
				if(c != null) {
					cust.add(c);
				}
			}
			resSet.getStatement().close();
		} catch (SQLException e) {
			Terminal.say("Stack trace: ", e);
		}
				
		return cust;
	}
	
	private Customer buildCustomer(ResultSet resSet)
	{
		Customer cust = null;
		try {
//			while(resSet.next())
//				{
					String phone = resSet.getString(this.phone);
					String name = resSet.getString(this.name);
					int saleID = resSet.getInt(this.saleID);
					DBSale dbs = new DBSale();
					cust = new Customer(phone, name, dbs.getSale("" + saleID, null));
//				}
		} catch (Exception e) {
			Terminal.say("An error occured while building Customer object", e);
		}
	
		
		return cust;
	}
}
